package chpt12.dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;

public class StudentDaoImpl implements StudentDao {

	@Override
	public Long addStudent(Student student) {
	    Connection con = null;
	    PreparedStatement pstmt = null;
	    // 未考虑student为null的情况
	    Long id = null;
	    try {
	        con = JDBCUtil.getConnection();
	        String sql = "INSERT INTO students (sno, name, gpa, bday) VALUES (?, ?, ?, ?)";
	        pstmt = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
	        pstmt.setString(1, student.getSno());
	        pstmt.setString(2, student.getName());
	        pstmt.setDouble(3, student.getGpa());
	        LocalDate bday = student.getBday();
	        if (bday != null) {
	        	pstmt.setDate(4, Date.valueOf(bday));
	        } else {
	        	pstmt.setNull(4, Types.DATE);
	        }
	        pstmt.executeUpdate();
	        // 获取刚插入记录的 ID
	        ResultSet generatedKeys = pstmt.getGeneratedKeys();
	        if (generatedKeys.next()) {
	            id = generatedKeys.getLong(1);
	        }
	    } catch (SQLException e) {
	        e.printStackTrace();
	    } finally {
	        JDBCUtil.closeAll(con, pstmt, null);
	    }
	    return id;
	}

    @Override
    public void updateStudentById(Student student) {
        Connection con = null;
        PreparedStatement pstmt = null;
        try {
            con = JDBCUtil.getConnection();
            String sql = "UPDATE students SET name = ?, gpa = ? WHERE id = ?";
            pstmt = con.prepareStatement(sql);
            pstmt.setString(1, student.getName());
            pstmt.setDouble(2, student.getGpa());
            pstmt.setLong(3, student.getId());
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.closeAll(con, pstmt, null);
        }
    }

    @Override
    public void deleteStudentById(Long id) {
        Connection con = null;
        PreparedStatement pstmt = null;
        try {
            con = JDBCUtil.getConnection();
            String sql = "DELETE FROM students WHERE id = ?";
            pstmt = con.prepareStatement(sql);
            pstmt.setLong(1, id);
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.closeAll(con, pstmt, null);
        }
    }

    @Override
    public Student getStudentById(Long id) {
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        Student student = null;
        try {
            con = JDBCUtil.getConnection();
            String sql = "SELECT * FROM students WHERE id = ?";
            pstmt = con.prepareStatement(sql);
            pstmt.setLong(1, id);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                student = new Student();
                student.setId(rs.getLong("id"));
                student.setSno(rs.getString("sno"));
                student.setName(rs.getString("name"));
                student.setGpa(rs.getDouble("gpa"));
                Date date = rs.getDate("bday");
                LocalDate d = date!=null?date.toLocalDate():null;
                student.setBday(d);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.closeAll(con, pstmt, rs);
        }
        return student;
    }

    @Override
    public List<Student> getAllStudents() {
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Student> students = new ArrayList<>();
        try {
            con = JDBCUtil.getConnection();
            String sql = "SELECT * FROM students";
            pstmt = con.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                Student student = new Student();
                student.setId(rs.getLong("id"));
                student.setSno(rs.getString("sno"));
                student.setName(rs.getString("name"));
                student.setGpa(rs.getDouble("gpa"));
                Date date = rs.getDate("bday");
                LocalDate d = date!=null?date.toLocalDate():null;
                student.setBday(d);
                students.add(student);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.closeAll(con, pstmt, rs);
        }
        return students;
    }

    @Override
    public List<Student> getStudentsByName(String name) {
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Student> students = new ArrayList<>();
        try {
            con = JDBCUtil.getConnection();
            String sql = "SELECT * FROM students WHERE name like ?";
            pstmt = con.prepareStatement(sql);
            pstmt.setString(1, "%"+name+"%");
            rs = pstmt.executeQuery();
            while (rs.next()) {
            	Student student = new Student();
                student.setId(rs.getLong("id"));
                student.setSno(rs.getString("sno"));
                student.setName(rs.getString("name"));
                student.setGpa(rs.getDouble("gpa"));
                Date date = rs.getDate("bday");
                LocalDate d = date!=null?date.toLocalDate():null;
                student.setBday(d);
                students.add(student);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.closeAll(con, pstmt, rs);
        }
        return students;
    }

}
